Let's take a look at another text based function.
Now I'm back in say the example where we talked about left right and Mid functions and something caught
my eye and maybe you caught it as well as you were working your way through the left right and Mid functions
looking at row number 19.
Within this list we've got this queue. A S 2 0 5 0 9 9 5.
You know what that value.
That's a much larger value than the rest of the Skewes year.
How many characters do I got there I got one two three four five six seven eight nine ten ten characters
that make up that skew.
Well you know what all the rest of my Skewes year they've got one two three four five six seven eight
characters.
If we go through and count all of these I believe that they're all eight with the exception of number
19.
Well when I'm using the same left and right and Mid functions that I've been using for all the rest
of them I'm grabbing the incorrect data.
I guess for this one I need the first three which represent the supplier.
I need the middle three to five which represents the part number and I need 0 9 9 5 to represent the
product code.
So there's actually four characters that make up the product code this time.
All right well how can I do this.
I guess I could go to this specific calculation that I created and equals right.
Ninety two.
I'm only grabbing two characters from the right hand side itself and I could change up to four.
No problem I'm done right was to do that change that from a two to four him-I anarchy.
And there we go.
All right I'm happy.
Everything looks good.
0 9 9 5 bucks.
That was me manually having to go through the calculations actually go to the Skewes check for those
ones that contain more or less characters and manually update the formula to reflect the difference
.
You know we could do that for a handful of records but you get in there with you know 50 plus hundreds
thousands of records and all of a sudden you're spending all day long going through and manually picking
apart these skews to find the ones that need to be different.
And we need to accommodate for this in our formula.
I want Excel to do this any time I can get Excel to do the work for me.
I again get Excel to do it.
Don't don't do this manually.
Let's take a look.
There is a function that I'm going to introduce you here called Lin L E N and it's short for length
.
It's going to tell us its sole purpose the sole purpose of the Liver Function is that tell us how many
characters are in a cell.
Take a look.
So I'm going to scroll back up here and I mean to create a new column here just into the age column
and we'll just call this number char's number of characters.
Make that a little wider.
All right.
So now I'm going to create this formula and it's a super simple one.
I'm inside of H.
I'm going to go to formulas go back in the text and I'll find the one called lead or length.
All right I want to know one thing.
This is why it's so simple.
One thing you want to know where the text is at.
Well for me the first row it's inside of a three.
I'll hit OK.
And this returns the number of characters length the length of characters within that cell in this case
within the A-3 So there's eight characters.
But let me copy that down and just don't double click in that little autofill box in the corner.
There it is.
A Oh there's 10.
Number 19.
That's the one we're talking about right.
0 9 9 5.
That one has 10 characters while the rest of them have eight.
This is the land function.
Very simple one.
It returns the number of characters from a cell.
This can be used for validation.
This could be used Hey we're trying to gather a phone number of affirmation from people or we're trying
to gather Social Security information from people that are some type of value that's set in size.
You give me your Social Security number your credit card number and know how many characters are inside
of those those values.
So make sure that it's 10 or 16 or 12 or having many characters we can quantify that and make sure that
we're collecting the correct number of values.
So there's multiple reasons we can use this.
But in this case I need to accommodate for this.
So here's I'm going to give you a homework assignment and I want you to jump into the discussion board
within this course and help us out.
How can we use that Lin function to use that in combination with something that we talked about earlier
in this course.
It's way way earlier we talked about it and one of the first videos in this course.
Now can we use this function Lin.
And one of the other ones we talked about earlier to help accommodate for differences that give you
a hint if it's eight characters then we should do it this way.
But if it's 10 characters we should do it this way.
Think about that for a moment.
How can we accommodate for this and get Excel to do it for us.
You're going to take two functions merge them together nest them and get Excel to do it for you.
So jump into the discussion board right Lisa comments in there let us know how you would do it.
As we start getting some comments all through my own two cents in there as well.
But I want you to think about this how can you do it.
Go